package com.csmz.mybookreader.DBSQLite;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.csmz.mybookreader.UserBean.UserInfo;

import java.sql.Blob;
import java.util.ArrayList;

public class DbMySQLite {
    private MyHelper helper;
    public DbMySQLite(Context context){
        helper=new MyHelper(context);
    }


    /**
     * 添加数据
     * @param nickname
     * @param year
     * @param gender
     * @param city
     */
    public void addUserInfo(String nickname, String year, String gender, String city ){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("insert into UserInfo(nickname,year,gender,city) values(?,?,?,?)",
                new Object[]{nickname, year, gender, city});
    }

    /**
     * 根据name值删除数据
     * @param nickname
     */
    public void deleteUserInfo(String nickname){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("delete from UserInfo where nickname=?",new Object[]{nickname});
    }

    /**
     * 根据name值修改表中数据
     * @param nickname
     * @param year
     * @param gender
     * @param city
     */
    public void updateUserInfo(String nickname, String year, String gender, String city){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("update student set year=?, gender=?,city=? where nickname=?",
                new Object[]{year, gender, city, nickname});
    }

    /**
     * 根据name值查询数据
     * @param nickname
     * @return
     */
    public UserInfo findUserInfo(String nickname){
        SQLiteDatabase db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select year,gender,city from UserInfo where nickname=?",
                new String[]{nickname});
        if(cursor.moveToNext()){
            UserInfo info = new UserInfo();
            info.setYear(cursor.getString(0));
            info.setGender(cursor.getString(1));
            info.setCity(cursor.getString(2));
            return info;
        }
        cursor.close();
        return null;
    }

    /**
     * 查询全部数据
     * @return
     */
    public ArrayList<UserInfo> findAllUserInfo(){
        SQLiteDatabase db = helper.getWritableDatabase();
        ArrayList<UserInfo> list = new ArrayList<UserInfo>();
        Cursor cursor = db.rawQuery("select * from UserInfo",null);
        for (int i = 0; i < cursor.getCount(); i++) {
            cursor.moveToNext();
            UserInfo info = new UserInfo();
            info.setNickname(cursor.getString(1));
            info.setGender(cursor.getString(4));
            info.setYear(cursor.getString(3));
            info.setCity(cursor.getString(5));
            list.add(info);
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 获取记录总数
     * @return
     */
    public long getCounts(){
        SQLiteDatabase db = helper.getWritableDatabase();
        Cursor cursor = db.query("UserInfo",new String[]{"count(*)"},null,null,null,null,null);
        if(cursor.moveToNext()){
            return cursor.getLong(0);
        }
        return 0;
    }

}
